package com.wuchaooooo.service.zjutsds.dao;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.wuchaooooo.service.zjutsds.pojo.po.PCareer;

/**
 * Created by arthur on 02/08/2020.
 */
@Repository
public class CareerDAO {
    private static final Logger LOGGER = LoggerFactory.getLogger(CareerDAO.class);

    @Resource(name = "primaryJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 相对于基本专业推荐，取得更多的专业推荐
     * 
     * @param pCareer
     * @return
     */
    public List<PCareer> getCareer2(PCareer pCareer) {
    	StringBuilder sql = new StringBuilder();
    	Map<String, Object> args  = new HashMap<>();
    	List<String> characters = Arrays.asList(pCareer.getCharacter().split(","));
    	 
    	sql.append("SELECT * FROM "+ getTable());
    	sql.append(" WHERE `subject` = :subject");
    	sql.append(" AND `gender` != :notGender");
    	sql.append(" AND (`course`  IN (:course)");
    	sql.append(" OR `character` IN (:character))");
    	sql.append(" AND `level` >= 3");
    	sql.append(" ORDER BY");
    	sql.append(" level desc,");
    	sql.append(" case when `course` IN (:course) then 0 else 1 end,");
    	sql.append(" case when `character` = :char1 then 1 ");
    	if (characters.size() > 1) {
    		args.put("char2", characters.get(1));
    		sql.append(" when `character` = :char2 then 2");
    	}
    	if (characters.size() > 2) {
            args.put("char3", characters.get(2)); 
        	sql.append(" when `character` = :char3 then 3");
    	}
    	if (characters.size() > 3) {
            args.put("char4", characters.get(3)); 
        	sql.append(" when `character` = :char4 then 4");
    	}
    	if (characters.size() > 4) {
            args.put("char5", characters.get(4)); 
        	sql.append(" when `character` = :char5 then 5");
    	}
    	if (characters.size() > 5) {
            args.put("char6", characters.get(5)); 
        	sql.append(" when `character` = :char6 then 6");
    	}
    	sql.append(" else 7");
    	sql.append(" end,");
    	sql.append(" majorCate");
    	sql.append(" limit 25");
        try {
        	NamedParameterJdbcTemplate nameJdbc = new NamedParameterJdbcTemplate(jdbcTemplate);

        	args.put("subject", pCareer.getSubject());
            args.put("notGender", "M".equals(pCareer.getGender())? "F" : "M");
            
            List<String> list = new ArrayList<String> ();
            Collections.addAll(list, pCareer.getCourse().split(","));
            list.add("");
            // 包括任何科
            args.put("course", list);
            args.put("character", characters);
            args.put("char1", characters.get(0));
            
            LOGGER.info("SQL语句：" + sql.toString());
            LOGGER.info("参数为：" + args.toString());
            return nameJdbc.query(sql.toString(), args, new BeanPropertyRowMapper<PCareer>(PCareer.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }
    
    /**
     * 取得基本专业推荐
     * 
     * @param pCareer
     * @return
     */
    public List<PCareer> getCareer1(PCareer pCareer) {
    	StringBuilder sql = new StringBuilder();
    	Map<String, Object> args  = new HashMap<>();
    	List<String> characters = Arrays.asList(pCareer.getCharacter().split(","));
    	 
    	sql.append("SELECT * FROM "+ getTable());
    	sql.append(" WHERE `subject` = :subject");
    	sql.append(" AND `gender` != :notGender");
    	sql.append(" AND `course`  IN (:course)");
    	sql.append(" AND `character` IN (:character)");
    	sql.append(" AND `level` >= 3");
    	sql.append(" ORDER BY");
    	sql.append(" level desc,");
    	sql.append(" case when `course` IN (:course) then 0 else 1 end,");
    	sql.append(" case when `character` = :char1 then 1 ");
    	if (characters.size() > 1) {
    		args.put("char2", characters.get(1));
    		sql.append(" when `character` = :char2 then 2");
    	}
    	if (characters.size() > 2) {
            args.put("char3", characters.get(2)); 
        	sql.append(" when `character` = :char3 then 3");
    	}
    	if (characters.size() > 3) {
            args.put("char4", characters.get(3)); 
        	sql.append(" when `character` = :char4 then 4");
    	}
    	if (characters.size() > 4) {
            args.put("char5", characters.get(4)); 
        	sql.append(" when `character` = :char5 then 5");
    	}
    	if (characters.size() > 5) {
            args.put("char6", characters.get(5)); 
        	sql.append(" when `character` = :char6 then 6");
    	}
    	sql.append(" else 7");
    	sql.append(" end,");
    	sql.append(" majorCate");
    	sql.append(" limit 25");
        try {
        	NamedParameterJdbcTemplate nameJdbc = new NamedParameterJdbcTemplate(jdbcTemplate);

        	args.put("subject", pCareer.getSubject());
            args.put("notGender", "M".equals(pCareer.getGender())? "F" : "M");
            
            List<String> list = new ArrayList<String> ();
            Collections.addAll(list, pCareer.getCourse().split(","));
            list.add("");
            // 包括任何科
            args.put("course", list);
            args.put("character", characters);
            args.put("char1", characters.get(0));
            
            LOGGER.info("SQL语句：" + sql.toString());
            LOGGER.info("参数为：" + args.toString());
            return nameJdbc.query(sql.toString(), args, new BeanPropertyRowMapper<PCareer>(PCareer.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }
    
    private String getTable() {
        return "career";
    }

}
